﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Bitirme.DAL.Entities;
using System.Data.SqlClient;
using Bitirme.DAL.Utils;
using System.Data;
using Bitirme.DAL.Enum;


namespace Bitirme.DAL.Data_Access
{
    public class PackageToProjectCollection
    {
        public static int InsertPackageToProject(WorkPackage workpackage, Project project)
        {
            SqlConnection myConnection = null;
            SqlCommand cmd = null;
            SqlParameter parProjectID = null;
            SqlParameter parWorkPackageID = null;
            SqlParameter parReturnValue = null;

            int insertedPackageToProjectID = -1;
            try
            {
                myConnection = SQLUtils.GetConnection();
                cmd = new SqlCommand("InsertPackageToProject", myConnection);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = myConnection; // ??

                parProjectID = new SqlParameter();
                parProjectID.ParameterName = "@projectID";
                parProjectID.Value = project.ID;
                parProjectID.SqlDbType = SqlDbType.Int;

                parWorkPackageID = new SqlParameter();
                parWorkPackageID.ParameterName = "@workPackageID";
                parWorkPackageID.Value = workpackage.ID;
                parWorkPackageID.SqlDbType = SqlDbType.Int;

                parReturnValue = new SqlParameter();
                parReturnValue.Direction = ParameterDirection.ReturnValue;
                parReturnValue.ParameterName = "@ID";

                cmd.Parameters.Add(parProjectID);
                cmd.Parameters.Add(parWorkPackageID);
                cmd.Parameters.Add(parReturnValue);

                myConnection.Open();
                cmd.ExecuteScalar();
                Int32.TryParse(parReturnValue.Value.ToString(), out insertedPackageToProjectID);
            }
            catch (SqlException e)
            {
                insertedPackageToProjectID = -1;
                Console.WriteLine(e);
            }
            finally
            {

                if (myConnection != null)
                {

                    myConnection.Close();
                    myConnection.Dispose();
                }

            }


            return insertedPackageToProjectID;
        }

        /*** Workpackagelar capability ve cost requirementla beraber getirirlir ***/
        public static List<WorkPackage> GetAllWorkPackagesByProjectID(int projectID) { 
           
            SqlConnection myConnection = null;
            SqlDataReader myReader = null;
            SqlCommand cmd = null;
            SqlParameter parProjectID = null;
            List<WorkPackage> workpackages = new List<WorkPackage>();

            try
            {
                myConnection = SQLUtils.GetConnection();
                cmd = new SqlCommand("GetWorkPackagesByProjectID", myConnection);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = myConnection;

                parProjectID = new SqlParameter();
                parProjectID.ParameterName = "@ID";
                parProjectID.Value = projectID;
                parProjectID.SqlDbType = SqlDbType.Int;

                cmd.Parameters.Add(parProjectID);

                myConnection.Open();
                myReader = cmd.ExecuteReader();
                while (myReader.Read())
                {
                    WorkPackage tempWorkPackage = new WorkPackage();
                    int ID;
                    Int32.TryParse(SQLUtils.NVL(myReader["workpackageID"], 0).ToString(), out ID);
                    tempWorkPackage.ID = ID;
                    tempWorkPackage.Name = SQLUtils.NVL(myReader["workpackageNAME"], String.Empty).ToString();
                    tempWorkPackage.Subject = SQLUtils.NVL(myReader["workpackageSUBJECT"], String.Empty).ToString();

                    PackageRequirement tempRequirement = new PackageRequirement();
                    int reqID;
                    Int32.TryParse(SQLUtils.NVL(myReader["requirementID"], 0).ToString(), out reqID);
                    tempRequirement.ID = reqID;
                    int cost;
                    Int32.TryParse(SQLUtils.NVL(myReader["requirementCOST"], 0).ToString(), out cost);
                    tempRequirement.Cost = cost;
                    int manhour;
                    Int32.TryParse(SQLUtils.NVL(myReader["requirementMANHOUR"], 0).ToString(), out manhour);
                    tempRequirement.ManHour = manhour;

                    Capability tempCapability = new Capability();
                    int capID;
                    Int32.TryParse(SQLUtils.NVL(myReader["capabilityID"], 0).ToString(), out capID);
                    tempCapability.ID = capID;
                    tempCapability.Name = SQLUtils.NVL(myReader["capabilityNAME"], String.Empty).ToString();

                    tempRequirement.RequirementCapability = tempCapability;
                    tempWorkPackage.Requirement = tempRequirement;

                    short state;
                    short.TryParse(SQLUtils.NVL(myReader["workpackageState"], 0).ToString(), out state);
                    switch (state)
                    {

                        case 0:
                            tempWorkPackage.PackageState = State.None;
                            break;
                        case 1:
                            tempWorkPackage.PackageState = State.Active;
                            break;
                        case 2:
                            tempWorkPackage.PackageState = State.Pasive;
                            break;
                        case 3:
                            tempWorkPackage.PackageState = State.Suspended;
                            break;
                        default:
                            tempWorkPackage.PackageState = State.None;
                            break;
                    }

                    workpackages.Add(tempWorkPackage);
                }
            }

            catch (SqlException e)
            {
                Console.WriteLine(e);
            }

            finally
            {
                if (myReader != null)
                {
                    myReader.Close();
                    myReader.Dispose();
                }

                if (myConnection != null)
                {
                    myConnection.Close();
                    myConnection.Dispose();
                }
            }

            return workpackages;
        }

        public static Boolean DeletePackagesToProjectByWorkPackageID(int workPackageID)
        {
            SqlConnection myConnection = null;
            SqlCommand cmd = null;
            SqlParameter parWorkPackageID = null;
            bool isDeleteSuccessful = false;

            try
            {
                myConnection = SQLUtils.GetConnection();
                cmd = new SqlCommand("DeletePackagesToProjectByWorkPackageID", myConnection);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = myConnection;

                parWorkPackageID = new SqlParameter();
                parWorkPackageID.ParameterName = "@ID";
                parWorkPackageID.Value = workPackageID;
                parWorkPackageID.SqlDbType = SqlDbType.Int;


                cmd.Parameters.Add(parWorkPackageID);


                myConnection.Open();

                cmd.ExecuteScalar();
                isDeleteSuccessful = true;
            }

            catch (SqlException e)
            {
                Console.WriteLine(e);
                isDeleteSuccessful = false;
            }

            finally
            {

                if (myConnection != null)
                {
                    myConnection.Close();
                    myConnection.Dispose();
                }
            }

            return isDeleteSuccessful;
        }
    
    }
}